-- 1. Ch?a c� b?ng tblLocation
-- 2. B?ng tblCustomer ko c� tr??ng Address, tham chi?u ??a ch? ? b?ng location
-- 3. B?ng tblBookTicketDetails tham chi?u tr??ng Origin, Destination v?i b?ng Location. 
--   Ko c� tr??ng locationId ? b?ng n�y

--------------------tblLoation------------------
Create table Location
(
LocationID integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
City varchar(100),
Country Varchar(100),
Address varchar(100),
Description varchar(200)
)
go
--------------------tblHotel--------------------
create table Hotel
(
HotelID integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
HotelName varchar (30) ,
LocationID integer 

)
go
Alter Table Hotel
add constraint FK_Hotel foreign key(LocationID) references Location(LocationID)
go
--------------------tblAirPlane--------------------
create table AirPlane
(
FlightID integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
Name varchar(30)
)
go
--------------------tblSeat--------------------
 create table Seat
(
SeatID integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
FlightID integer,
SeatName varchar (50),
Status varchar(50)
)
go
Alter Table Seat
add constraint FK_Seat foreign key(FlightID) references AirPlane(FlightID)
go
--------------------tblCustomer--------------------
Create table Customer
(CustomerID integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
CustomerName varchar (50),
Email varchar (30),
Phone varchar(20)
)
go
--------------------tblUser--------------------
create table Users
(UserID integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
UserName varchar(100),
PassWord varchar (100)
)
go
--------------------tblBookTicket--------------------
create table BookTicket
(BookTicketID integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
CustomerID integer,
TotalTicket integer,
TotalPrice float,
OrderDate varchar(10),
CreditCardNumber integer ,
IsPay char for bit data
)
go
Alter Table  Bookticket
add constraint FK_Customer1 foreign key(CustomerID) references  Customer(CustomerID)
go

--------------------tblCity--------------------
create table City
(
CityId integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
CityName varchar(100)
)

--------------------tblTicket--------------------
create table Ticket
(
TicketId integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
FlightId integer,
JourneyDate date,
Origin integer,
Destination integer,
NumberSeats integer,
Price float
)

Alter Table Ticket
add constraint FK_Ticket_AirPlane foreign key(FlightId) references AirPlane(FlightId)
Alter Table Ticket
add constraint FK_Ticket_City1 foreign key(Origin) references City(CityId)
Alter Table Ticket
add constraint FK_Ticket_City2 foreign key(Destination) references City(CityId)

--------------------tblBookTicketDetails--------------------
create table BookTicketDetails
(BookticketDetailsID integer primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
BookTicketID integer,
CustomerID integer,
TicketId integer,
NumberTicket integer,
Totalprice float
)
go
Alter Table BookTicketDetails
add constraint FK_Customer2 foreign key(CustomerID) references Customer(CustomerID)
go
Alter Table  BookTicketDetails
add constraint FK_Ticket foreign key(TicketId ) references  Ticket(TicketId)
Alter Table  BookTicketDetails
add constraint FK_BookTicket foreign key(BookTicketID ) references  BookTicket(BookTicketID)
